﻿using KPIS.DBM;
using KPIS.GERP.GAMS.MODEL;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;

namespace KPIS.GERP.GAMS.DAL
{
    public class D_UnitCost
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public DataTable GetProjectCost(string bgm_year)
        {
            DataTable dtb = null;
            string strSQL = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_year", bgm_year);

                strSQL = "select exp.bgm_exp_seq, exp.bgm_exp_code, exp.bgm_exp_name"
                    + ", pj.bgm_proj_seq, pj.bgm_proj_code"
                    + ", sum(case when pje.bgm_source_seq = 1 then pje.amount else 0 end) as budget_amount1"
                    + ", sum(case when pje.bgm_source_seq = 11 then pje.amount else 0 end) as budget_amount2"
                    + ", sum(case when rpt.bgm_source_seq = 1 then rpt.amount else 0 end) as used_amount1"
                    + ", sum(case when rpt.bgm_source_seq = 11 then rpt.amount else 0 end) as used_amount2"

                    + " from bgm_project pj"
                    + " inner join (select pje.bgm_proj_seq, pje.bgm_exp_seq, pje.bgm_source_seq"
	                    + ", sum(coalesce(pje.total_amount, 0)) as amount"
	                    + " from bgm_project_expense pje"
	                    + " group by pje.bgm_proj_seq, pje.bgm_exp_seq, pje.bgm_source_seq"
	                    + ") pje on pj.bgm_proj_seq = pje.bgm_proj_seq"
                    + " inner join (select pje.bgm_proj_seq, pje.bgm_exp_seq, pje.bgm_source_seq"
	                    + ", sum(coalesce(mrd.amount, 0)) as amount"
	                    + " from fim_payments p"
	                    + " left join fim_money_request_dtls mrd on p.money_req_seq = mrd.money_req_seq"
	                    + " left join bgm_project_operation_expense ope on mrd.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq"
	                    + " left join bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq"
	                    + " where p.record_status = 'A'"
	                    + " group by pje.bgm_proj_seq, pje.bgm_exp_seq, pje.bgm_source_seq"
	                    + ") rpt on pje.bgm_proj_seq = rpt.bgm_proj_seq"
	                    + " and pje.bgm_exp_seq = rpt.bgm_exp_seq"
	                    + " and pje.bgm_source_seq = rpt.bgm_source_seq"
                    + " inner join bgm_mas_expense exp on rpt.bgm_exp_seq = exp.bgm_exp_seq"

                    + " where pj.record_status not like 'D'"
                    + " and pj.bgm_year = @bgm_year"

                    + " group by exp.bgm_exp_seq, exp.bgm_exp_code, exp.bgm_exp_name"
                        + ", pj.bgm_proj_seq, pj.bgm_proj_code"

                    + " order by exp.bgm_exp_code, pj.bgm_proj_code";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dtb;
        }

        public DataTable GetSubActCost(string bgm_year)
        {
            DataTable dtb = null;
            string strSQL = "";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@bgm_year", bgm_year);

                strSQL = "select exp.bgm_exp_seq, exp.bgm_exp_code, exp.bgm_exp_name"
                    + ", j.bgm_strat_list_seq, j.bgm_strat_list_name"
                    + ", pje1.amount1 as budget_amount1"
                    + ", pje1.amount2 as budget_amount2"
                    + ", pje2.amount1 as budget2_amount1"
                    + ", pje2.amount2 as budget2_amount2"
                    + ", sum(case when rpt.bgm_source_seq = 1 then rpt.amount else 0 end) as used_amount1"
                    + ", sum(case when rpt.bgm_source_seq = 11 then rpt.amount else 0 end) as used_amount2"

                    + " from bgm_project pj"
                    + " inner join (select pje.bgm_proj_seq, pje.bgm_exp_seq, pje.bgm_source_seq"
	                    + ", sum(coalesce(mrd.amount, 0)) as amount"
	                    + " from fim_payments p"
	                    + " left join fim_money_request_dtls mrd on p.money_req_seq = mrd.money_req_seq"
	                    + " left join bgm_project_operation_expense ope on mrd.bgm_proj_oper_exp_seq = ope.bgm_proj_oper_exp_seq"
	                    + " left join bgm_project_expense pje on ope.bgm_proj_exp_seq = pje.bgm_proj_exp_seq"
	                    + " where p.record_status = 'A'"
	                    + " group by pje.bgm_proj_seq, pje.bgm_exp_seq, pje.bgm_source_seq"
	                    + ") rpt on pj.bgm_proj_seq = rpt.bgm_proj_seq"

                    + " inner join (select pje.bgm_exp_seq"
	                    + ", sum(case when pje.bgm_source_seq = 1 then pje.total_amount else 0 end) as amount1"
	                    + ", sum(case when pje.bgm_source_seq = 11 then pje.total_amount else 0 end) as amount2"
	                    + " from bgm_project_expense pje"
	                    + " group by pje.bgm_exp_seq"
	                    + ") pje1 on rpt.bgm_exp_seq = pje1.bgm_exp_seq"

                    + " inner join (select pj.bgm_strat_list_seq"
	                    + ", sum(case when pje.bgm_source_seq = 1 then pje.total_amount else 0 end) as amount1"
	                    + ", sum(case when pje.bgm_source_seq = 11 then pje.total_amount else 0 end) as amount2"
	                    + " from bgm_project_expense pje"
	                    + " inner join bgm_project pj on pje.bgm_proj_seq = pj.bgm_proj_seq"
	                    + " group by pj.bgm_strat_list_seq"
	                    + ") pje2 on pj.bgm_strat_list_seq = pje2.bgm_strat_list_seq"

                    + " inner join bgm_mas_expense exp on rpt.bgm_exp_seq = exp.bgm_exp_seq"
                    + " inner join bgm_strategy_data_list j on pj.bgm_strat_list_seq = j.bgm_strat_list_seq"

                    + " where pj.record_status not like 'D'"
                    + " and pj.bgm_year = @bgm_year"

                    + " group by exp.bgm_exp_seq, exp.bgm_exp_code, exp.bgm_exp_name"
                        + ", j.bgm_strat_list_seq, j.bgm_strat_list_name"
                        + ", pje1.amount1, pje1.amount2"
                        + ", pje2.amount1, pje2.amount2"

                    + " order by exp.bgm_exp_code, j.bgm_strat_list_seq;";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dtb;
        }

        public DataTable GetDepreCost(string bgm_year)
        {
            DataTable dtb = null;
            string strSQL = "";
            int nYear = string.IsNullOrEmpty(bgm_year) ? DateTime.Today.Year : Convert.ToInt32(bgm_year) - 543;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@start_date", new DateTime(nYear - 1, 10, 1));
                dbManager.AddParameters(1, "@end_date", new DateTime(nYear, 9, 30));

                strSQL = "select acc.account_seq, acc.account_code, acc.account_name"
                    + ", coalesce(j.job_site_seq, 9999999999) as job_site_seq"
                    + ", coalesce(j.job_site_name, 'อยู่ในคลังพัสดุ') as job_site_name"
                    + ", sum(coalesce(dep.DepAmount, 0)) as dep_amount"
                    + " from IcmAssetDepreciation dep"
                    + " left join IcmAsset ass on dep.AssetId = ass.AssetId"
                    + " left join IcmMasResourceGroup res on ass.ResGrpId = res.ResGrpId"
                    + " left join acm_mas_account acc on res.DepreAccountId = acc.account_seq"
                    + " left join hrm_mp_job_sites j on ass.JobSiteId = j.job_site_seq"
                    + " where dep.RecordStatus not like 'D'"
                    + " and dep.CalculatedDate >= @start_date"
                    + " and dep.CalculatedDate <= @end_date"
                    + " group by acc.account_seq, acc.account_code, acc.account_name"
                    + ", j.job_site_seq, j.job_site_name"

                    + " order by acc.account_code, j.job_site_seq";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dtb;
        }
        
    }
}
